# -*- coding: utf-8 -*-
# 导入pymysql模块
import pymysql

import pandas as pd 
import numpy as np

from MysqlHelp import DB


# def get_fmdata(fm, starttime, endtime,deviceidlist):
#     # print(type(fm))
#
#     # 连接database
#     conn = pymysql.connect(host="47.92.33.19", user="root",password="1qazxsw2",database="database_fm",charset="utf8")
#     # 得到一个可以执行SQL语句的光标对象
#     cursor = conn.cursor()
#     # 定义要执行的SQL语句
#
#     # sql = '''SELECT a.id, b.device_lgid,a.frequency,d.lon,d.lat,a.signal_strength,c.area,c.lon,c.lat,DATE_FORMAT(a.upload_time,'%Y-%c-%d %H:%i:%S') AS upload_time FROM fm_t_scan_record a
#     # LEFT JOIN fm_t_task_record b ON a.task_record_id = b.id
#     # LEFT JOIN fm_t_device d ON b.device_lgid = d.phy_id
#     # LEFT JOIN fm_t_whitelist c ON a.frequency = c.frequency
#     # where a.frequency ='''+ fm +''' AND DATE_FORMAT(a.upload_time,'%Y-%c-%d %H:%i:%S') BETWEEN \''''+ starttime + '''\' AND \''''+ endtime+ '''\' ORDER BY a.frequency'''
#
#     # sql = '''select a.id,a.deviceid,a.frequency,a.lon,a.lat,a.signal_strength,a.relation_area,a.fm_lon,a.fm_lat,a.upload_time
#     # from (
#     # select r.id,r.deviceid,r.frequency,r.lon,r.lat,r.signal_strength,l.relation_area,l.lon as fm_lon,l.lat as fm_lat,r.upload_time,
#     #  @num := if(@course = r.deviceid, @num + 1, 1) as row_number,
#     #  @course := r.deviceid as dummy
#     # from fm_t_scan_record r left join fm_v_legal_list l on l.frequency=r.frequency and LOCATE(r.deviceId,''' + deviceidlist + ''')>0
#     #    where r.frequency= '''+ fm + ''' AND locate(r.sound_markup,'music,human')>0 and r.signal_strength > -90 and r.signal_strength < -40
#     #     and DATE(r.upload_time)>'2019-11-14'
#     #   order by r.deviceid, r.id desc
#     # ) as a where a.row_number <= 3'''
#     if fm in ['88.7','102.7']:
#         sql = '''select a.id,a.deviceid,a.frequency,a.lon,a.lat,a.signal_strength,a.relation_area,a.fm_lon,a.fm_lat,a.upload_time
#         from (
#         select r.id,r.deviceid,r.frequency,r.lon,r.lat,r.signal_strength,l.relation_area,l.lon as fm_lon,l.lat as fm_lat,r.upload_time,
#          @num := if(@course = r.deviceid, @num + 1, 1) as row_number,
#          @course := r.deviceid as dummy
#         from fm_t_scan_record r left join fm_v_legal_list l on l.frequency=r.frequency and LOCATE(r.deviceId,l.relation_devices)>0
#            where r.frequency= '''+ fm + ''' and if(LOCATE(r.frequency,'87.9,88.7,89.9,90.9,91.4,93.4,94.0,94.7,95.9,97.2,97.7,98.1,99.0,100.1,100.3,100.9,101.7,102.5,102.7,103.7,105.1,105.7,106.5,106.7,107.2,107.7,96.2,95.5')>0 ,
#            1=1 ,locate(sound_markup,'music,human')>=0 ) and r.signal_strength > -85
#              -- and r.deviceid <> '123456780001713'
#             and DATE(r.upload_time)>DATE_SUB(now(),INTERVAL 10 DAY) and LOCATE(r.deviceid,\'''' + deviceidlist +'''\') > 0 and r.lon > 116.0
#           order by r.deviceid, r.id desc
#         ) as a where a.row_number < 10'''
#     else:
#         sql = '''select a.id,a.deviceid,a.frequency,a.lon,a.lat,a.signal_strength,a.relation_area,a.fm_lon,a.fm_lat,a.upload_time
#         from (
#         select r.id,r.deviceid,r.frequency,r.lon,r.lat,r.signal_strength,l.relation_area,l.lon as fm_lon,l.lat as fm_lat,r.upload_time,
#          @num := if(@course = r.deviceid, @num + 1, 1) as row_number,
#          @course := r.deviceid as dummy
#         from fm_t_scan_record r left join fm_v_legal_list l on l.frequency=r.frequency and LOCATE(r.deviceId,l.relation_devices)>0
#            where r.frequency= ''' + fm + ''' and if(LOCATE(r.frequency,'87.9,88.7,89.9,90.9,91.4,93.4,94.0,94.7,95.9,97.2,97.7,98.1,99.0,100.1,100.3,100.9,101.7,102.5,102.7,103.7,105.1,105.7,106.5,106.7,107.2,107.7,96.2,95.5')>0 ,
#            1=1 ,locate(sound_markup,'music,human')>=0 ) and r.signal_strength > -85 and r.signal_strength < -40
#              -- and r.deviceid <> '123456780002911' and r.deviceid <> '123456780001832'
#             and DATE(r.upload_time)>DATE_SUB(now(),INTERVAL 10 DAY) and LOCATE(r.deviceid,\'''' + deviceidlist +'''\') > 0 and r.lon > 116.0
#           order by r.deviceid, r.id desc
#         ) as a where a.row_number < 10'''
#
#     try:
#         cursor.execute(sql)
#         # 关闭光标对象
#         sql_result = cursor.fetchall()
#     except Exception as ex:
#         raise ex
#     finally:
#         cursor.close()
#         # 关闭数据库连接
#         conn.close()
#
#     re = pd.DataFrame(np.resize(sql_result,(len(sql_result),10)), columns=['id','deviceid','fm','lon','lat','db','local','fmlon','fmlat','time'])
#     return re
def get_fmdata(fm, starttime, endtime,deviceidlist):
    # 连接database
    conn = pymysql.connect(host="47.92.33.19", user="root",password="1qazxsw2",database="database_fm",charset="utf8")
    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor()
    # 定义要执行的SQL语句
    args = [fm,deviceidlist]


    try:

        # 关闭光标对象
        cursor.callproc('proc_to_rssi_location_fm_new',args)
        sql_result = cursor.fetchall()
        conn.commit()
    except Exception as ex:
        raise ex
    finally:
        cursor.close()
        # 关闭数据库连接
        conn.close()
    re = pd.DataFrame(np.resize(sql_result,(len(sql_result),11)), columns=['id','deviceid','fm','lon','lat','db','local','fmlon','fmlat','time','determine'])
    return re
def get_fmtdevice():
    # 连接database
    conn = pymysql.connect(host="47.92.33.19", user="root",password="1qazxsw2",database="database_fm",charset="utf8")
    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor()

    sql = '''SELECT phy_id,lon,lat FROM fm_t_device'''

  
    try:
        cursor.execute(sql)
        # 关闭光标对象
        sql_result = cursor.fetchall()
    except Exception as ex:
        raise ex
    finally:
        cursor.close()
        # 关闭数据库连接
        conn.close()

    re = pd.DataFrame(np.resize(sql_result,(len(sql_result),3)), columns=['deviceid','lon','lat'])
    return re